﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections;

/*
 * Author:Conis
 * Date:
 * Description:执行数据库操作的基类
 */

namespace Conis.Squirrel.SqlBuilder
{
    public abstract class BaseExecuteSql
    {
        /// <summary>
        /// 数据库的连接
        /// </summary>
        public IDbConnection Connection { get; set; }
        /// <summary>
        /// 事务
        /// </summary>
        protected IDbTransaction Transaction { get; set; }

        /// <summary>
        /// 获取一个Command
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="commandType"></param>
        /// <param name="paramArray"></param>
        /// <returns></returns>
        protected abstract IDbCommand GetCommand(string sql, CommandType commandType, params IDataParameter[] paramArray);
        /// <summary>
        /// 填充数据到DataTable
        /// </summary>
        /// <param name="cmd"></param>
        /// <returns></returns>
        protected abstract DataTable FillToTable(IDbCommand cmd);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection">数据库的连接</param>
        /// <param name="transaction">事务</param>
        public BaseExecuteSql(IDbConnection connection, IDbTransaction transaction)
        {
            this.Connection = connection;
            this.Transaction = transaction;
        }

        /// <summary>
        /// 执行一条SQL语句，返回查询结果到DataReader中
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="commandType">SQL语句的类型</param>
        /// <param name="paramArray">传入的参数</param>
        /// <returns>返回查询结果</returns>
        public virtual IDataReader GetReader(string sql, CommandType commandType, params IDataParameter[] paramArray)
        {
            IDbCommand cmd = this.GetCommand(sql, commandType, paramArray);
            IDataReader reader = cmd.ExecuteReader();
            //只有在示启用事务的情况下，才关闭连接
            if (this.Transaction == null)
            {
                this.Connection.Close();
            }
            cmd.Dispose();
            return reader;
        }

        /// <summary>
        /// 执行一条SQL语句，返回查询结果到DataTable中
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="commandType">SQL语句的类型</param>
        /// <param name="paramArray">传入的参数</param>
        /// <returns>返回查询结果</returns>
        public virtual DataTable GetDataTable(string sql, CommandType commandType, params IDataParameter[] paramArray)
        {
            IDbCommand cmd = this.GetCommand(sql, commandType, paramArray);
            DataTable dt = this.FillToTable(cmd);
            //只有在示启用事务的情况下，才关闭连接
            if (this.Transaction == null)
            {
                this.Connection.Close();
            }
            cmd.Dispose();
            return dt;
        }

        /// <summary>
        /// 执行一条SQL语句，返回第一行第一列
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="commandType">SQL语句的类型</param>
        /// <param name="paramArray">传入的参数</param>
        /// <returns>返回第一行第一列</returns>
        public virtual object ExecuteScalar(string sql, CommandType commandType, params IDataParameter[] paramArray)
        {
            IDbCommand cmd = this.GetCommand(sql, commandType, paramArray);
            object result = cmd.ExecuteScalar();
            //只有在示启用事务的情况下，才关闭连接
            if (this.Transaction == null)
            {
                this.Connection.Close();
            }
            cmd.Dispose();
            return result;
        }

        /// <summary>
        /// 执行一条SQL语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">要执行的SQL语句</param>
        /// <param name="commandType">SQL语句的类型</param>
        /// <param name="paramArray">传入的参数</param>
        /// <returns>返回受影响的行数</returns>
        public virtual int ExecuteNonQuery(string sql, CommandType commandType, params IDataParameter[] paramArray)
        {
            IDbCommand cmd = this.GetCommand(sql, commandType, paramArray);
            int result = cmd.ExecuteNonQuery();
            //只有在示启用事务的情况下，才关闭连接
            if (this.Transaction == null)
            {
                this.Connection.Close();
            }
            cmd.Dispose();
            return result;
        }

        /// <summary>
        /// 执行一批SQL语句
        /// </summary>
        /// <param name="sqlList"></param>
        public virtual void ExecuteNonQuery(string[] sqlList)
        {
            IDbCommand cmd = this.GetCommand("", CommandType.Text);
            foreach (string sql in sqlList)
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
            //只有在示启用事务的情况下，才关闭连接
            if (this.Transaction == null)
            {
                this.Connection.Close();
            }
            cmd.Dispose();
        }
    }
}
